MasterCashflow3
1. Description
Master Cashflow 3 cashflow type generates cashflows for each record based on the mapping provided for component,cashflow_amount,due_date,outstanding_amount,maturity_date.
2. Screen Configuration
Click ⬇️ to download the test-bed.
3. Cashflow Derivation Logic
Case 1: If 'Is Overdue CF Required' is mapped as true,
ACCOUNT_ID|MASTER_AMOUNT|CASHFLOW_AMOUNT|CASHFLOW_AMOUNT_TYPE|DUE_DATE|MATURITY_DATE
ACC1001|5000.00|500.00|PRINCIPAL|31-01-2024|31-05-2024
ACC1001|5000.00|50.00|INTEREST|29-02-2024|28-02-2024
ACC1001|5000.00|50.00|PRINCIPAL|31-03-2024|31-05-2024
ACC1001|5000.00|50.00|PRINCIPAL|31-05-2024|30-04-2024
ACC1001|5000.00|50.00|INTEREST|31-05-2024|31-05-2024
for generation of cashflows, it compares cashflow_amount_type, if PRINCIPAL then stamp the cashflow_amount in the principal_amount and interest_amount will be stamped as 0.0 else if INTEREST then the cashflow_amount in the interest_amount and principal_amount will be stamped as 0.0.
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
500.00|0.0|31-01-2024
0.0|50.00|29-02-2024
50.00|0.0|31-03-2024
50.00|0.0|30-04-2024
0.0|50.00|31-05-2024
Case 2: If 'Is Overdue CF Required' is mapped as false, it compares the due_date and maturity_date, if due_date <= maturity_date it skips those records.
ACCOUNT_ID|MASTER_AMOUNT|CASHFLOW_AMOUNT|CASHFLOW_AMOUNT_TYPE|DUE_DATE|MATURITY_DATE
ACC1001|5000.00|500.00|PRINCIPAL|31-01-2024|31-05-2024
ACC1001|5000.00|50.00|INTEREST|29-02-2024|28-02-2024
ACC1001|5000.00|50.00|PRINCIPAL|31-03-2024|31-05-2024
ACC1001|5000.00|50.00|PRINCIPAL|31-05-2024|30-04-2024
ACC1001|5000.00|50.00|INTEREST|31-05-2024|31-05-2024
for generation of cashflows, it compares cashflow_amount_type, if PRINCIPAL then stamp the cashflow_amount in the principal_amount and interest_amount will be stamped as 0.0 else if INTEREST then the cashflow_amount is stamped in the interest_amount and principal_amount will be stamped as 0.0.
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
500.00|0.0|31-01-2024
50.00|0.0|31-03-2024
0.0|50.00|31-05-2024
Here the second and fourth cashflow record are skipped since is_overdue_cf_req is set true and the due_date of second and fourth record is less than the maturity_date.
4. Required Fields
| # | Parameters | Description | Is_Mandatory_Field | Possible_Values |
|---|---|---|---|---|
| 1 | cashflow_account_id | The field which needs to be considered as the key to store the cashflows. | YES | - |
| 2 | cashflow_amount | The amount field which needs to be stamped as principal/interest amount based on cashflow_amount_type. | YES | - |
| 3 | is_overdue_cashflow_req | The flag which will decided if overdue cashflow is required or not. | YES | - |
| 4 | cashflow_date | The date field which needs to stamped as cashflow date. | YES | - |
| 5 | cashflow_amount_type | The field which decides the type of cashflow amount (principal/interest). | YES | - |
| 6 | master_account_id | The field which needs to be stamped as account id, and to be used to do lookup on cashflow file | YES | - |
| 7 | master_amount | The total outstanding amount of the account. | YES | - |
| 8 | master_maturity_date | The date field which needs to be considered as account end date. | YES | - |
| 9 | is_master_amount_principal | The flag to decide if the cashflow amount is to be set as principal amount or not. | NO | true or false |
| 10 | interest_cashflow_type_values | Values for interest cashflow type. | NO | - |
| 11 | principal_cashflow_type_values | Values for principal cashflow type. | NO | - |
5. Working Excel
Click ⬇️ to download the excel calculation.